package com.arrcen.medicen.core.match.service.impl;

import com.alibaba.fastjson.JSONObject;

import com.arrcen.medicen.core.match.helper.HttpHelper;
import com.arrcen.medicen.core.match.helper.OracleHelper;
import com.arrcen.medicen.core.match.model.dto.DtoPage;
import com.arrcen.medicen.core.match.model.dto.DtoPageKey;
import com.arrcen.medicen.core.match.model.dto.DtoSaveTable;
import com.arrcen.medicen.core.match.model.dto.ResultObj;
import com.arrcen.medicen.core.match.model.entity.*;
import com.arrcen.medicen.core.match.service.*;
import com.arrcen.medicen.core.match.utlity.EntityHelper;
import com.arrcen.medicen.core.match.utlity.LogHelper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class ScheduleTaskImp implements ScheduleTask {
    @Resource
    LogHelper logHelper;

    //连接数据库的参数
    @Value("${driver-class-name}")
    String driver;

    @Value("${source.url}")
    String url;
    @Value("${source.username}")
    String username;
    @Value("${source.password}")
    String passwd;

    @Value("${target.url}")
    String targetUrl;
    @Value("${target.username}")
    String targetUsername;
    @Value("${target.password}")
    String targetPasswd;


    @Resource
    ScheduleTaskService scheduleTaskService;

    @Resource
    MyScheduleTaskTableService scheduleTaskTableService;

    @Resource
    SyncTableService syncTableService;

    @Resource
    SyncTableFieldService syncTableFieldService;

    EntityHelper<ScheduleTaskEntity> eh = new EntityHelper<>(new ScheduleTaskEntity());
    EntityHelper<ScheduleTaskTableEntity> ehTaskField = new EntityHelper<>(new ScheduleTaskTableEntity());


    @Override
    public IPage<ScheduleTaskEntity> getTaskList(DtoPage dtoPage) {
        Page<ScheduleTaskEntity> page = scheduleTaskService.page(new Page<>(dtoPage.page, dtoPage.rows), null);
        return page;
    }

    @Override
    public IPage<ScheduleTaskTableEntity> getTaskTableList(DtoPageKey dtoPage) {
        ResultObj<ScheduleTaskTableEntity> resultObj = new ResultObj<>();
        LambdaQueryWrapper<ScheduleTaskTableEntity> wrapper = Wrappers.lambdaQuery(ScheduleTaskTableEntity.class);
        wrapper.eq(ScheduleTaskTableEntity::getTaskId, dtoPage.getKey());

        Page<ScheduleTaskTableEntity> page = scheduleTaskTableService.page(new Page<>(dtoPage.getPage(), dtoPage.getRows()), wrapper);
        return page;
    }

    /**
     * 同步数据，流程如下
     * 1、从表SYNC_TABLE里取出需同步的表(用statu==1)
     * 2、创建任务记录日志
     * 3、循环需同步的表
     * 4、从SYNC_TABLE_FIELD表取出要同步的字段
     * 5、拼接查询语句，判断目标库字段是否存在,并从源库查出数据
     * 5.1循环查出的数据，判断目标该数据是否存在，如果存则，则更新源表需更新的字段
     * 5.2如不存在，则调用数据添加接口，调用成功后，再更新源表数据需更新的字段
     * 5.3记录第个表的同步日志，并保存
     * 6、汇总本交任务的日志。并保存
     */
    @Override
    public void xxlJobRun() {
        //1、从表SYNC_TABLE里取出需同步的表，每次同步20张表
        List<SyncTableEntity> tableAll = syncTableService.list(SyncTableEntity.class, x -> x.status == 1, 1, 20, null);
        logHelper.info(ScheduleTaskImp.class, "查询出" + tableAll.size() + "条需同步的表");
        if (tableAll.size() == 0) return;
        //2、创建任务记录日志
        ScheduleTaskEntity scheduleEntity = new ScheduleTaskEntity();
        scheduleEntity.setDateStart(LocalDateTime.now());
        scheduleEntity.setTaskId(scheduleTaskService.getIncreasingId(ScheduleTaskEntity.class));
        scheduleEntity.setNumFail(0);
        scheduleEntity.setNumSucc(0);
        scheduleEntity.setLog("");
        logHelper.info(ScheduleTaskImp.class, "计算出TaskId");
        //3、循环需同步的表
        for (SyncTableEntity tableEntity : tableAll) {
            if (tableEntity.getPageSize()==null || tableEntity.getPageSize() < 0) tableEntity.setPageSize(10);
            //4、从SYNC_TABLE_FIELD表取出要同步的字段
            int sync_table_id = tableEntity.getSyncTableId();
            List<SyncTableFieldEntity> allField = syncTableFieldService.list(SyncTableFieldEntity.class, x -> x.syncTableId == sync_table_id, 1, 100, null);
            //主键字段 1表示关联的字段
            List<SyncTableFieldEntity> keyList = allField.stream().filter(x -> x.getOpType() == 1).collect(Collectors.toList());
            //同步的字段 ,2表示同步的字段
            List<SyncTableFieldEntity> SyncList = allField.stream().filter(x -> x.getOpType() == 2).collect(Collectors.toList());
            //回写的字段,3表示需回写
            List<SyncTableFieldEntity> saveList = allField.stream().filter(x -> x.getOpType() == 3).collect(Collectors.toList());
            logHelper.info(ScheduleTaskImp.class,
                    String.format("请取%s表的配置%s列", tableEntity.getName(), allField.size()));

            //5、拼接查询语句，判断目标库字段是否存在,并从源库查出数据
            ScheduleTaskTableEntity scheduleTaskTableEntity = new ScheduleTaskTableEntity();
            scheduleTaskTableEntity.setTaskTableId(scheduleTaskTableService.getIncreasingId(ScheduleTaskTableEntity.class));
            scheduleTaskTableEntity.setDateStart(LocalDateTime.now());
            scheduleTaskTableEntity.setTaskId(scheduleEntity.getTaskId());
            scheduleTaskTableEntity.setSyncTableId(tableEntity.getSyncTableId());
            scheduleTaskTableEntity.setNumFail(0);
            scheduleTaskTableEntity.setNumSucc(0);
            scheduleTaskTableEntity.setLog("");
            logHelper.info(ScheduleTaskImp.class,
                    String.format("计算出TaskTableId"));
            /**
             * 合并要查询的字段
             */
            List<SyncTableFieldEntity> sumField=new ArrayList<>();
            sumField.addAll(SyncList);
            sumField.addAll(keyList);
            String fieldStr=String.join(",", sumField.stream().map(x -> x.getTmpFieldName()).collect(Collectors.toList()));

            String sqlSelectTmp= String.format("SELECT COUNT(1) num FROM (SELECT COUNT(1) FROM %s  GROUP BY %s)",tableEntity.getTmpTableName(),fieldStr);
            if (saveList.size() > 0) {
                sqlSelectTmp= String.format("SELECT COUNT(1) num FROM (SELECT COUNT(1) FROM %s WHERE %s GROUP BY %s)",
                        tableEntity.getTmpTableName(),
                        String.join(" or ", saveList.stream().map(x -> x.getTmpFieldName() + " is null ").collect(Collectors.toList())),
                        fieldStr);
            }
            OracleHelper.init(driver, url, username, passwd);
            ResultSet rs=OracleHelper.executeQuery(sqlSelectTmp,null);
            try {
                int allNum = 0;
                int allPage = 0;
                if (rs.next()) {
                    allNum = rs.getInt("num");
                }
                rs.close();

                allPage = allNum / tableEntity.getPageSize();
                if (allNum % tableEntity.getPageSize() > 0) allPage++;
                boolean succ = false;
                /**
                 * 需要回写的集合
                 */
                List<HashMap<String, Object>> saveBackList=new ArrayList<>();
                List<HashMap<String, String>> saveBackKeyList=new ArrayList<>();
                for (int page = 0; page < allPage; page++) {
                    logHelper.info(ScheduleTaskImp.class, String.format("开始同步数据 %s/%s",page,allPage));

                    if (saveList.size() > 0) {
                        sqlSelectTmp = String.format(
                                "SELECT %s FROM %s WHERE %s GROUP BY %s",
                                fieldStr,
                                tableEntity.getTmpTableName(),
                                String.join(" or ", saveList.stream().map(x -> x.getTmpFieldName() + " is null ").collect(Collectors.toList())),
                                fieldStr
                                );
                    } else {
                        sqlSelectTmp = String.format(
                                "SELECT %s FROM %s GROUP BY %s",
                                fieldStr,
                                tableEntity.getTmpTableName(),
                                fieldStr
                                );
                    }

                    sqlSelectTmp=String.format("SELECT * FROM (SELECT ROWNUM RN,x.* FROM ( %s ) x ) WHERE RN>%s and RN<=%s",sqlSelectTmp, page*tableEntity.getPageSize(),(page+1)*tableEntity.getPageSize());
                    OracleHelper.init(driver, url, username, passwd);
                    rs = OracleHelper.executeQuery(sqlSelectTmp, null);


                    //用于保存同步表的日志

                    //要同步的数据集合
                    List<HashMap<String, String>> mapSaveList = new ArrayList<>();
                    //要主键的数据集合
                    List<HashMap<String, String>> mapKeyList = new ArrayList<>();
                    //本地的判断标识
                    List<HashMap<String, String>> mapKeyTmpList = new ArrayList<>();

                    //读取多条要同步的数据
                    while (rs.next()) {
                        //须同步的HashMap
                        HashMap<String, String> mapSave = new HashMap<>();
                        //中心库的判断标识
                        HashMap<String, String> mapKey = new HashMap<>();
                        //本地的判断标识
                        HashMap<String, String> mapKeyTmp = new HashMap<>();

                        for (int i = 0; i < SyncList.size(); i++) {
                            //5.1循环查出的数据，判断目标该数据是否存在，如果存则，则更新源表需更新的字段
                            SyncTableFieldEntity fieldEntity = SyncList.get(i);
                            Object val = rs.getObject(fieldEntity.getTmpFieldName());
                            mapSave.put(fieldEntity.getCenFieldName(), val.toString());
                        }

                        for (int i = 0; i < keyList.size(); i++) {
                            //5.1循环查出的数据，判断目标该数据是否存在，如果存则，则更新源表需更新的字段
                            SyncTableFieldEntity fieldEntity = keyList.get(i);
                            String val = rs.getString(fieldEntity.getTmpFieldName());
                            mapKey.put(fieldEntity.getCenFieldName(), val);
                            mapKeyTmp.put(fieldEntity.getTmpFieldName(), val);
                        }
                        mapSaveList.add(mapSave);
                        mapKeyList.add(mapKey);
                        mapKeyTmpList.add(mapKeyTmp);
                    }

                    //读取完数据，关闭数据连接
                    rs.close();

                    //开始同步数据,并返回需回写的数据
                    if (mapSaveList.size() > 0) {
                        try {
                            /**
                             * 调用保存数据方法
                             */
                            logHelper.info(ScheduleTaskImp.class,String.format("开始调用保存数据方法，执行%s条数据",mapSaveList.size()));
                            List<HashMap<String, Object>> saveTempMapList = syncData(tableEntity.getApiUrl(), tableEntity.getCenTableName(), mapSaveList, mapKeyList, saveList);
                            //成功后保存数据
                            logHelper.info(ScheduleTaskImp.class,String.format("执行结束，返回%s条需回写的数据",saveTempMapList.size()));
                            saveBackList.addAll(saveTempMapList);
                            saveBackKeyList.addAll(mapKeyTmpList);

                        } catch (Exception e) {
                            succ = false;
                            scheduleTaskTableEntity.setLog(scheduleTaskTableEntity.getLog() + "\r\n" + e.getMessage());
                        }
                    } else {
                        logHelper.info(sqlSelectTmp);
                        logHelper.info(String.format("无数据需同步"));
                    }
                }

                List<String> sqlList = new ArrayList<>();
                for (int i = 0; i < saveBackList.size(); i++) {
                    HashMap<String, Object> saveTempMap = saveBackList.get(i);
                    HashMap<String, String> hashMap = saveBackKeyList.get(i);
                    if (saveTempMap == null || saveTempMap.size() == 0) {
                        succ = false;
                        scheduleTaskTableEntity.setNumFail(scheduleTaskTableEntity.getNumFail() + 1);
                    } else {
                        scheduleTaskTableEntity.setNumSucc(scheduleTaskTableEntity.getNumSucc() + 1);
                        succ = true;
                        String sql = String.format(
                                "UPDATE %s SET %s WHERE %s",
                                tableEntity.getTmpTableName(),
                                String.join(" , ", saveTempMap.keySet().stream().map(x -> x + "='" + saveTempMap.get(x) + "'").collect(Collectors.toList())),
                                String.join(" and ", hashMap.keySet().stream().map(x -> x + "='" + hashMap.get(x) + "'").collect(Collectors.toList()))
                        );
                        sqlList.add(sql);
                    }
                }
                logHelper.info(String.format("同步完成,需回写数据条数为%s",sqlList.size()));
                OracleHelper.init(driver, url, username, passwd);
                OracleHelper.insertList(sqlList);

                //5.3记录第个表的同步日志，并保存
                scheduleTaskTableEntity.setDateEnd(LocalDateTime.now());
                scheduleTaskTableService.getBaseMapper().insert(scheduleTaskTableEntity);
                logHelper.info(ScheduleTaskImp.class,
                        String.format("保存作业表日志"));
                //结束同步数据
                if (succ) {
                    scheduleEntity.setNumSucc(scheduleEntity.getNumSucc() + 1);
                } else {
                    scheduleEntity.setNumFail(scheduleEntity.getNumFail() + 1);
                }
            } catch (SQLException e) {
                System.out.println(sqlSelectTmp);
                e.printStackTrace();
                scheduleEntity.setLog(e.getMessage());
            }
        }
        scheduleEntity.setDateEnd(LocalDateTime.now());
        eh.data = scheduleEntity;
        //6、汇总本交任务的日志。并保存
        scheduleTaskService.getBaseMapper().insert(scheduleEntity);
        logHelper.info(ScheduleTaskImp.class,
                String.format("保存作业日志"));
    }

    /**
     * 同步数据到中心库
     *
     * @param syncApiUrl  接口地址
     * @param tableName   要中心库的表名
     * @param mapSaveList 要保存的字段
     * @param mapKeyList  判断的主键
     * @param saveList    要保存的字段
     * @return 是否操作成功
     */
    private List<HashMap<String, Object>> syncData(String syncApiUrl, String tableName, List<HashMap<String, String>> mapSaveList, List<HashMap<String, String>> mapKeyList, List<SyncTableFieldEntity> saveList) throws SQLException {
        if (mapKeyList.size() < 1) return null;

        //需要回写的数据
        List<HashMap<String, Object>> mapSaveTmpList = new ArrayList<>();
        //需保存到远程的数据
        List<HashMap<String, String>> mapSaveListRemote = new ArrayList<>();

        OracleHelper.init(driver, targetUrl, targetUsername, targetPasswd);
        //查出需回写的数据，如果数据不存在，则放入到mapSaveListRemote，第一次同步，不需要回写
        for (int y = 0; y < mapSaveList.size(); y++) {
            HashMap<String, String> mapKey = mapKeyList.get(y);
            if(saveList.size()>0) {
                String sqlSelectCent = String.format(
                        "SELECT %s FROM %s WHERE %s",
                        String.join(",", saveList.stream().map(x -> x.getCenFieldName()).collect(Collectors.toList())),
                        tableName,
                        String.join(" and ", mapKey.keySet().stream().map(x -> x + "='" + mapKey.get(x) + "'").collect(Collectors.toList()))
                );
                ResultSet rs = OracleHelper.executeQuery(sqlSelectCent, null);

                //5.1循环查出的数据，判断目标该数据是否存在，如果存则，则更新源表需更新的字段
                if (rs.next()) {
                    HashMap<String, Object> hashMap = new HashMap<>();
                    for (int i = 0; i < saveList.size(); i++) {
                        SyncTableFieldEntity fieldEntity = saveList.get(i);
                        Object val = rs.getObject(fieldEntity.getCenFieldName());
                        hashMap.put(fieldEntity.getTmpFieldName(), val);
                    }
                    mapSaveTmpList.add(hashMap);
                } else {
                    //同步数据
                    HashMap<String, String> map = mapSaveList.get(y);
                    map.putAll(mapKeyList.get(y));
                    mapSaveListRemote.add(map);
                }
                rs.close();
            }else {
                //同步数据
                HashMap<String, String> map = mapSaveList.get(y);
                map.putAll(mapKeyList.get(y));
                mapSaveListRemote.add(map);
            }
        }


        if (mapSaveListRemote.size() > 0) {
            DtoSaveTable saveTable = new DtoSaveTable();
            saveTable.setReKeyList(saveList.stream().map(x -> x.getCenFieldName()).collect(Collectors.toList()));
            saveTable.setTableName(tableName);
            saveTable.setSaveMapList(mapSaveListRemote);
            String s = HttpHelper.doPostJson(syncApiUrl, JSONObject.toJSONString(saveTable));

            if(saveList.size()>0) {
                // 5.2如不存在，则调用数据添加接口，调用成功后，再更新源表数据需更新的字段
                //同步完数据，再读取一次，中心库的数据，读取回写字段
                for (int y = 0; y < mapSaveListRemote.size(); y++) {
                    HashMap<String, String> mapKey = mapKeyList.get(y);
                    String sqlSelectCent = String.format(
                            "SELECT %s FROM %s WHERE %s",
                            String.join(",", saveList.stream().map(x -> x.getCenFieldName()).collect(Collectors.toList())),
                            tableName,
                            String.join(" and ", mapKey.keySet().stream().map(x -> x + "='" + mapKey.get(x) + "'").collect(Collectors.toList()))
                    );
                    ResultSet rs = OracleHelper.executeQuery(sqlSelectCent, null);

                    //5.1循环查出的数据，判断目标该数据是否存在，如果存则，则更新源表需更新的字段
                    if (rs.next()) {
                        HashMap<String, Object> hashMap = new HashMap<>();
                        for (int i = 0; i < saveList.size(); i++) {
                            SyncTableFieldEntity fieldEntity = saveList.get(i);
                            Object val = rs.getObject(fieldEntity.getCenFieldName());
                            hashMap.put(fieldEntity.getTmpFieldName(), val);
                        }
                        mapSaveTmpList.add(hashMap);
                    }
                    rs.close();
                }
            }
        }

        return mapSaveTmpList;

    }
}
